package com.wap.framework.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.wap.framework.pagination.PaginationHelper;
import com.wap.framework.model.User;

public class UserManageDAOImpl implements UserManageDAO {
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	private static String countSql = "select count(*) from dbo.tb_user where i_delflag=0";
	private static String countSqlByStatus = "select count(*) from dbo.tb_user where i_delflag=0 and i_status=?";
	private static String listCurrentPage = "select * from (select *,row_number() over(order by i_uid) as rownum from dbo.tb_user where i_delflag=0) as t where t.rownum>? and t.rownum<=?";
	private static String listByStatus = "select * from (select *,row_number() over(order by i_uid) as rownum from dbo.tb_user where i_status=? and i_delflag=0) as t where t.rownum>? and t.rownum<=?";
	private static String updateUserStatus = "update dbo.tb_user set i_status=? where i_uid=?";

	@Override
	public void addUser(User user) {

	}

	@Override
	public void deleteUser(int[] ids) {

	}

	@Override
	public User editUser(int id) {
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object> listUsers(int pageNum) {
		PaginationHelper ph = new PaginationHelper(pageNum);
		ph.setJdbcTemplate(jdbcTemplate);
		ph.init(countSql);

		Object[] params = new Object[] { ph.getStartIndex(), ph.getEndIndex() };
		int[] types = new int[] { Types.INTEGER, Types.INTEGER };
		List<User> users = jdbcTemplate.query(listCurrentPage, params, types,
				new UserRowMapper());

		List result = new ArrayList(2);
		result.add(users);
		result.add(ph);

		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object> listUsersByStatus(int pageNum, int status) {
		PaginationHelper ph = new PaginationHelper(pageNum);
		ph.setJdbcTemplate(jdbcTemplate);
		ph.init(countSqlByStatus, status);

		Object[] params = new Object[] { status, ph.getStartIndex(),
				ph.getEndIndex() };
		int[] types = new int[] { Types.INTEGER, Types.INTEGER, Types.INTEGER };
		List<User> users = jdbcTemplate.query(listByStatus, params, types,
				new UserRowMapper());

		List result = new ArrayList(2);
		result.add(users);
		result.add(ph);

		return result;
	}

	@Override
	public void updateUser(final Integer[] ids, final Integer[] status) {
		BatchPreparedStatementSetter pstmt = new BatchPreparedStatementSetter() {
			public int getBatchSize() {
				return ids.length;
			}

			public void setValues(PreparedStatement pstmt, int i)
					throws SQLException {
				pstmt.setInt(1, status[i]);
				pstmt.setInt(2, ids[i]);
			}
		};
		this.jdbcTemplate.batchUpdate(updateUserStatus, pstmt);
	}

	class UserRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			User user = new User();
			user.setUid(rs.getInt("i_uid"));
			user.setUsername(rs.getString("v_username"));
			user.setPassword(rs.getString("v_password"));
			user.setCellPhone(rs.getString("v_cellphone"));
			user.setSex(rs.getString("v_sex"));
			user.setAge(rs.getInt("i_age"));
			user.setEmail(rs.getString("v_email"));
			user.setStatus(rs.getInt("i_status"));
			user.setScore(rs.getInt("i_score"));
			return user;
		}
	}
}
